# encoding=utf-8


import pandas as pd
import pymysql

year_season_map = {
    "1985": "85-86",
    "1986": "86-87",
    "1987": "87-88",
    "1988": "88-89",
    "1989": "89-90",
    "1990": "90-91",
    "1991": "91-92",
    "1992": "92-93",
    "1993": "93-94",
    "1994": "94-95",
    "1995": "95-96",
    "1996": "96-97",
    "1997": "97-98",
    "1998": "98-99",
    "1999": "99-00",
    "2000": "00-01",
    "2001": "01-02",
    "2002": "02-03",
    "2003": "03-04",
    "2004": "04-05",
    "2005": "05-06",
    "2006": "06-07",
    "2007": "07-08",
    "2008": "08-09",
    "2009": "09-10",
    "2010": "10-11",
    "2011": "11-12",
    "2012": "12-13",
    "2013": "13-14",
    "2014": "14-15",
    "2015": "15-16",
    "2016": "16-17",
}


def init_mysql():
    global conn
    conn = pymysql.connect('localhost', user="root", passwd="123456", db="nba")
    global cur
    cur = conn.cursor()


def deinit_mysql():
    cur.close()
    conn.close()


def write_players_to_mysql(players):
    sql = "insert into players (name) values (%s);"
    for player in players:
        cur.execute(sql, player)
    conn.commit()


def write_seasons_to_mysql(seasons):
    sql = "insert into seasons (name) values (%s);"
    for season in seasons:
        cur.execute(sql, season)
    conn.commit()


def build_history_best_teams():
    team_all = get_team_all_data()
    teams_grouped = team_all.groupby('球队')
    best_team_win_rank = teams_grouped.agg({'结果': 'sum'}) \
        .rename({"结果": "count"}, axis=1).sort_values(by="count", ascending=False).reset_index()
    best_team_score_rank = teams_grouped.agg({'得分': 'mean'}) \
        .rename({"得分": "count"}, axis=1).sort_values(by="count", ascending=False).reset_index()
    best_team_home_win_rank = team_all.loc[team_all['主/客场'] == '主'].groupby('球队').agg({'结果': 'sum'}) \
        .rename({"结果": "count"}, axis=1).sort_values(by="count", ascending=False).reset_index()
    best_team_away_win_rank = team_all.loc[team_all['主/客场'] == '客'].groupby('球队').agg({'结果': 'sum'}) \
        .rename({"结果": "count"}, axis=1).sort_values(by="count", ascending=False).reset_index()
    best_team_lose_rank = teams_grouped.agg({'失分': 'sum'}) \
        .rename({"失分": "count"}, axis=1).sort_values(by="count").reset_index()

    best_team_list = [
        {"name": best_team_win_rank.loc[0, "球队"], "count": best_team_win_rank.loc[0, "count"], "type": "win_rank"},
        {"name": best_team_score_rank.loc[0, "球队"], "count": best_team_score_rank.loc[0, "count"],
         "type": "score_rank"},
        {"name": best_team_home_win_rank.loc[0, "球队"], "count": best_team_home_win_rank.loc[0, "count"],
         "type": "home_win_rank"},
        {"name": best_team_away_win_rank.loc[0, "球队"], "count": best_team_away_win_rank.loc[0, "count"],
         "type": "away_win_rank"},
        {"name": best_team_lose_rank.loc[0, "球队"], "count": best_team_lose_rank.loc[0, "count"], "type": "lose__rank"}]

    best_team_df = pd.DataFrame(best_team_list)
    best_team_df.to_csv("./data/mid_data/best_teams_csv")


def get_history_best_teams():
    best_teams = pd.read_csv("./data/mid_data/best_teams_csv", index_col=0)
    return {
        "win_rank": {
            "name": best_teams.loc[0, "name"],
            "count": int(best_teams.loc[0, "count"])
        },
        "score_rank": {
            "name": best_teams.loc[1, "name"],
            "count": round(best_teams.loc[1, "count"], 2)
        },
        "home_win_rank": {
            "name": best_teams.loc[2, "name"],
            "count": int(best_teams.loc[2, "count"])
        },
        "away_win_rank": {
            "name": best_teams.loc[3, "name"],
            "count": int(best_teams.loc[3, "count"])
        },
        "lose_rank": {
            "name": best_teams.loc[4, "name"],
            "count": int(best_teams.loc[4, "count"])
        }
    }


def get_history_best_players():
    players = pd.read_csv("./data/ori_data/player_season.csv")
    players_grouped = players.groupby("球员")
    best_players_score = \
        players_grouped.agg({'得分': 'sum'}).sort_values(by="得分", ascending=False).reset_index()
    best_players_backboard = players_grouped.agg({'篮板': 'sum'}).sort_values(by="篮板", ascending=False) \
        .reset_index()
    best_players_three_point = players_grouped.agg({'三分命中': 'sum'}).sort_values(by="三分命中", ascending=False) \
        .reset_index()

    return {
        "score": {"name": best_players_score.loc[0, "球员"], "count": int(best_players_score.loc[0, "得分"])},
        "three_point": {"name": best_players_three_point.loc[0, "球员"],
                        "count": int(best_players_three_point.loc[0, "三分命中"])},
        "backboard": {"name": best_players_score.loc[0, "球员"], "count": int(best_players_backboard.loc[0, "篮板"])}
    }


def build_team_all_data():
    team_season = pd.read_csv("./data/ori_data/team_season.csv", index_col=0)
    team_playoff = pd.read_csv("./data/ori_data/team_playoff.csv", index_col=0)
    team_all = pd.concat([team_season, team_playoff], axis=0, ignore_index=True)
    team_all['对手'] = team_all['比分'].apply(lambda x: x[0:3])
    team_all['失分'] = team_all['比分'].str.extract('([0-9]{2,3})-').astype('int')
    team_all['年份'] = team_all['时间'].str.extract('([0-9]{4})').astype('int')
    team_all['赛季'] = team_all['时间'].str.extract('([0-9]{4})')
    team_all['赛季'] = team_all['赛季'].map(year_season_map)
    team_all['时间'] = pd.to_datetime(team_all['时间'])
    team_all['结果'] = team_all['结果'].map({'W': 1, 'L': 0})
    team_all.to_csv("./data/mid_data/team_all.csv")


def get_team_all_data():
    return pd.read_csv("./data/mid_data/team_all.csv")


# def build_teams_history():
# team_all = build_teams_data()
# teams = team_all['球队'].unique()
# teams_history = pd.DataFrame()
# for team in teams:
#     result = team_all[team_all["球队"] == team].groupby('赛季') \
#         .agg({'结果': 'sum', '得分': 'mean', '出手': 'mean', '助攻': 'mean'
#                  , '三分': 'mean', '盖帽': 'mean', '篮板': 'mean', '犯规': 'mean'
#                  , '抢断': 'mean'}) \
#         .sort_values("赛季", ascending=False) \
#         .reset_index()
#
#     result['球队'] = team
#     result['得分'] = result['得分'].astype(int)
#     result['出手'] = result['出手'].astype(int)
#     result['助攻'] = result['助攻'].astype(int)
#     result['盖帽'] = result['盖帽'].astype(int)
#     result['篮板'] = result['篮板'].astype(int)
#     result['犯规'] = result['犯规'].astype(int)
#     result['抢断'] = result['抢断'].astype(int)
#     result['三分'] = round(result['三分'], 2)
#     # print(result)
#     teams_history = pd.concat([teams_history, result], ignore_index=True)
# teams_history.to_csv("./data/result_data/teams_history.csv")

def get_team_list():
    team_playoff = pd.read_csv("./data/ori_data/team_playoff.csv")
    team_list = team_playoff["球队"].unique().tolist()
    return team_list


def get_team_history(team_name):
    team_all = get_team_all_data()
    team_all = team_all[team_all["球队"] == team_name]
    team_all = team_all.groupby(["赛季", "年份"], as_index=False) \
        .agg({"得分": "sum", "结果": "sum", "出手": "sum",
              "助攻": "sum", "三分": "sum", "盖帽": "sum",
              "篮板": "sum", "犯规": "sum", "抢断": "sum"}) \
        .sort_values("年份", ascending=False) \
        .head(6)
    result_list = []
    for _, history in team_all.iterrows():
        result = {
            "year": int(history['年份']),
            "score": int(history['得分']),
            "win": int(history['结果']),
            "shot": int(history['出手']),
            "assist": int(history['助攻']),
            "three_point": round(history['三分'], 2),
            "cap": int(history['盖帽']),
            "backboard": int(history['篮板']),
            "foul": int(history['犯规']),
            "steal": int(history['抢断'])
        }
        result_list.append(result)

    return result_list


def get_team_player_salary(team_name):
    player_salary = pd.read_csv("./data/ori_data/player_salary.csv", index_col=0).fillna(0)
    player_salary = player_salary[player_salary["team"] == team_name]
    player_salary = player_salary.groupby("name").max("salary").reset_index()
    # player_salary = player_salary[player_salary["season"] == "16-17"]  # 取16-17赛季的做为标准
    player_salary = player_salary.sort_values("salary", ascending=False).head(6)
    # print(player_salary)
    max_salary = player_salary["salary"].max()
    # print(max_salary)

    player_season = pd.read_csv("./data/ori_data/player_season.csv", index_col=0)
    player_season["team"] = player_season["比分"].str.extract('([A-Z]{3})$')
    # player_season = player_season[player_season["赛季"] == "16-17"]
    player_season = player_season.groupby("球员").sum("得分").reset_index()

    player_list = []
    for index, player in player_salary.iterrows():
        score = player_season.loc[player_season["球员"] == player["name"], "得分"]
        if score.empty:
            continue
        player_list.append({
            "name": player["name"],
            "salary": player["salary"],
            "score": int(score),
            "percent": round(player["salary"] / max_salary, 2),
        })
    return player_list


# def get_team_aggregation(team_name):
#     aggregation_analysis = pd.read_csv("./data/result_data/aggregation_analysis.csv")
#
#     aggregation_analysis = aggregation_analysis[aggregation_analysis["球队"] == team_name]
#
#     result = {
#         "average_win": aggregation_analysis.loc[0, '平均胜场'],
#         "average_score": aggregation_analysis.loc[0, '平均得分'],
#         "average_win_rate": aggregation_analysis.loc[0, '平均胜率'],
#     }
#
#     return result

def get_season_list():
    player_season = pd.read_csv("./data/ori_data/player_season.csv")
    season_list = player_season["赛季"].unique().tolist()
    return season_list


def get_season_best_players(season_name):
    players = pd.read_csv("./data/ori_data/player_season.csv", index_col=0)
    players = players[players["赛季"] == season_name]
    players_grouped = players.groupby("球员")
    players = players_grouped.agg({'得分': 'sum', '篮板': 'sum', '三分命中': 'sum', '助攻': 'sum', '抢断': 'sum', '盖帽': 'sum'}) \
        .sort_values(by="得分", ascending=False) \
        .reset_index()
    players["mvp"] = players['得分'] + players['篮板'] + players['助攻'] + players['抢断'] + players['盖帽']

    best_players_score = players.sort_values(by="得分", ascending=False).head(1).reset_index()
    best_players_backboard = players.sort_values(by="篮板", ascending=False).head(1).reset_index()
    best_players_three_point = players.sort_values(by="三分命中", ascending=False).head(1).reset_index()
    best_players_assist = players.sort_values(by="助攻", ascending=False).head(1).reset_index()
    best_players_mvp = players.sort_values(by="mvp", ascending=False).head(1).reset_index()
    # print(best_players_score)
    # print(best_players_backboard)
    # print(best_players_three_point)
    # print(best_players_assist)
    # print(best_players_mvp)

    return {
        "mvp": {"name": best_players_mvp.loc[0, "球员"], "count": int(best_players_mvp.loc[0, "mvp"])},
        "score": {"name": best_players_score.loc[0, "球员"], "count": int(best_players_score.loc[0, "得分"])},
        "backboard": {"name": best_players_backboard.loc[0, "球员"], "count": int(best_players_backboard.loc[0, "篮板"])},
        "three_point": {"name": best_players_three_point.loc[0, "球员"],
                        "count": int(best_players_three_point.loc[0, "三分命中"])},
        "assist": {"name": best_players_assist.loc[0, "球员"], "count": int(best_players_assist.loc[0, "助攻"])}
    }


# 获取赛季per前六名球员数据
def get_season_best_players_rank(season_name):
    top6_players = pd.read_csv("./data/ori_data/advanced_basic.csv", index_col=0).fillna(0)
    top6_players = top6_players[top6_players["赛季"] == season_name].sort_values("WS", ascending=False).head(6)
    player_list = []
    for index, player in top6_players.iterrows():
        player_list.append({
            "name": player["球员"],
            "per": round(player["PER"], 2),
            "attack": int(player["进攻效率"]),
            "defence": int(player["防守效率"]),
            "ws": round(player["WS"]),
            "rank": index
        })
    return player_list


# 获取季后赛最激烈队伍top10
def get_season_playoff_team_top(season_name):
    team_playoff = pd.read_csv("./data/ori_data/team_playoff.csv", index_col=0).fillna(0)
    team_playoff['赛季'] = team_playoff['时间'].str.extract('([0-9]{4})')
    team_playoff['赛季'] = team_playoff['赛季'].map(year_season_map)
    team_playoff = team_playoff[team_playoff["赛季"] == season_name]
    team_playoff['los'] = team_playoff['比分'].str.extract('([0-9]{2,3})-').astype('int')
    team_playoff["win_los_diff"] = abs(team_playoff["得分"] - team_playoff["los"])
    team_playoff["win_los_diff_less_5"] = team_playoff["win_los_diff"].apply(lambda x: 1 if x < 5 else 0)
    team_playoff = team_playoff.groupby("球队").agg({"win_los_diff_less_5": "sum"}) \
        .sort_values("win_los_diff_less_5", ascending=False).reset_index().head(10)

    team_list = []
    for _, team in team_playoff.iterrows():
        team_list.append({
            "name": team["球队"],
            "win_los_diff": int(team["win_los_diff_less_5"])
        })
    return team_list


def get_season_team_rank(season_name):
    team_all = get_team_all_data()
    team_all = team_all[team_all["赛季"] == season_name]
    score_rank = team_all.groupby("球队").agg({"得分": "sum"}).sort_values("得分", ascending=False).reset_index().head(6)
    win_rank = team_all.groupby("球队").agg({"结果": "sum"}).sort_values("结果", ascending=False).reset_index().head(6)
    cap_rank = team_all.groupby("球队").agg({"盖帽": "sum"}).sort_values("盖帽", ascending=False).reset_index().head(6)
    los_rank = team_all.groupby("球队").agg({"失分": "sum"}).sort_values("失分").reset_index().head(6)

    win_rank_list = []
    for index, team in win_rank.iterrows():
        win_rank_list.append({"name": team["球队"], "count": team["结果"], "rank": index})

    score_rank_list = []
    for index, team in score_rank.iterrows():
        score_rank_list.append({"name": team["球队"], "count": team["得分"], "rank": index})

    cap_rank_list = []
    for index, team in cap_rank.iterrows():
        cap_rank_list.append({"name": team["球队"], "count": team["盖帽"], "rank": index})

    los_rank_list = []
    for index, team in los_rank.iterrows():
        los_rank_list.append({"name": team["球队"], "count": team["失分"], "rank": index})

    return {
        "win_rank": win_rank_list,
        "score_rank": score_rank_list,
        "cap_rank": cap_rank_list,
        "los_rank": los_rank_list
    }


def get_player_list():
    advanced_basic = pd.read_csv("./data/ori_data/advanced_basic.csv", index_col=0).fillna(0)
    player_list = advanced_basic["球员"].unique().tolist()
    return player_list


def get_player_season_data(player_name, season_name):
    players = pd.read_csv("./data/ori_data/player_season.csv", index_col=0).fillna(0)
    players = players[players["赛季"] == season_name]
    players = players[players["球员"] == player_name]

    if len(players) == 0:
        return {}

    backboard = int(players["篮板"].mean())
    assist = int(players["助攻"].mean())
    mistake = int(players["失误"].mean())
    score = int(players["得分"].mean())
    cap = int(players["盖帽"].mean())
    steal = int(players["抢断"].mean())
    three_point_hit = round(players["三分命中率"].mean(), 2)
    penalty_hit = round(players["罚球命中率"].mean(), 2)

    players = pd.read_csv("./data/ori_data/advanced_shooting.csv", index_col=0).fillna(0)
    players = players[players["赛季"] == season_name]
    players = players[players["球员"] == player_name]
    players["近距命中率"] = players["近距命中"] / players["近距出手"]
    players["中距命中率"] = players["中距命中"] / players["中距出手"]
    players["远距命中率"] = players["远距命中"] / players["远距出手"]

    backboard_hit = round(players["篮下命中率"].mean(), 2)
    near_hit = round(players["近距命中率"].mean(), 2)
    middle_hit = round(players["中距命中率"].mean(), 2)
    long_hit = round(players["远距命中率"].mean(), 2)

    return {'backboard_hit': backboard_hit,
            'near_hit': near_hit,
            'middle_hit': middle_hit,
            'long_hit': long_hit,
            'penalty_hit': penalty_hit,
            'three_point_hit': three_point_hit,
            'backboard': backboard,
            'assist': assist,
            'mistake': mistake,
            'score': score,
            'cap': cap,
            'steal': steal}


def get_player_history_agg(player_name):
    players = pd.read_csv("./data/ori_data/player_season.csv", index_col=0)
    players = players[players["球员"] == player_name]

    backboard = round(players["篮板"].mean(), 2)
    assist = round(players["助攻"].mean(), 2)
    mistake = round(players["失误"].mean(), 2)
    score = round(players["得分"].mean(), 2)
    cap = round(players["盖帽"].mean(), 2)
    steal = round(players["抢断"].mean(), 2)
    three_point_hit = round(players["三分命中率"].mean(), 2)
    penalty_hit = round(players["罚球命中率"].mean(), 2)

    players = pd.read_csv("./data/ori_data/advanced_shooting.csv", index_col=0)
    players = players.fillna(0)
    players = players[players["球员"] == player_name]
    players["近距命中率"] = players["近距命中"] / players["近距出手"]
    players["中距命中率"] = players["中距命中"] / players["中距出手"]
    players["远距命中率"] = players["远距命中"] / players["远距出手"]

    backboard_hit = round(players["篮下命中率"].mean(), 2)
    near_hit = round(players["近距命中率"].mean(), 2)
    middle_hit = round(players["中距命中率"].mean(), 2)
    long_hit = round(players["远距命中率"].mean(), 2)

    return {'backboard_hit': backboard_hit,
            'near_hit': near_hit,
            'middle_hit': middle_hit,
            'long_hit': long_hit,
            'penalty_hit': penalty_hit,
            'three_point_hit': three_point_hit,
            'backboard': backboard,
            'assist': assist,
            'mistake': mistake,
            'score': score,
            'cap': cap,
            'steal': steal}


def get_player_history_data(player_name):
    advanced_basic = pd.read_csv("./data/ori_data/advanced_basic.csv", index_col=0).fillna(0)
    advanced_shooting = pd.read_csv("./data/ori_data/advanced_shooting.csv", index_col=0).fillna(0)

    players = pd.merge(advanced_basic, advanced_shooting, on=["球员", "赛季", "球队"])
    players = players[players["球员"] == player_name]
    player_years = players.reset_index().head(6)

    year_list = []
    for _, year in player_years.iterrows():
        year_list.append({
            "year": year["赛季"],
            "per": round(year["PER"], 2),
            "shot_rate": round(year["真实命中率"], 2)
        })
    return year_list


def build_teams_history_data():
    team_all = get_team_all_data()
    teams = team_all['球队'].unique()
    # print(teams)
    teams_history_data = pd.DataFrame()
    for team in teams:
        team_agg = team_all[team_all["球队"] == team]
        team_agg["总场"] = 1
        result = team_agg.groupby('赛季').agg({'结果': 'sum', '得分': 'mean', '总场': 'sum'}) \
            .sort_values("赛季", ascending=False) \
            .reset_index()

        total_win = result['结果'].sum()
        average_win = total_win / result['赛季'].count()
        average_score = result['得分'].sum() / result['赛季'].count()
        average_win_rate = total_win / result['总场'].sum()
        average_win = round(average_win, 2)
        average_score = round(average_score, 2)
        average_win_rate = round(average_win_rate, 2)
        team_history_data = pd.DataFrame(
            {'球队': [team], '总胜场': total_win, '平均胜场': [average_win], '平均得分': [average_score],
             '平均胜率': [average_win_rate]})
        teams_history_data = pd.concat([teams_history_data, team_history_data], ignore_index=True)
    teams_history_data.to_csv("./data/mid_data/team_history_data.csv")


def get_teams_history_data():
    team_history_data = pd.read_csv("./data/mid_data/team_history_data.csv", index_col=0)

    team_list = []
    for _, team in team_history_data.iterrows():
        team_list.append({"name": team["球队"],
                          "total_win": int(team["总胜场"]),
                          "average_win": round(team["平均胜场"], 2),
                          "average_score": round(team["平均得分"], 2),
                          "average_win_rate": round(team["平均胜率"], 2)})

    return team_list


# 生成中间数据表
def build_mid_data():
    build_team_all_data()
    build_history_best_teams()
    build_teams_history_data()


if __name__ == '__main__':
    # team_all = build_teams_data()
    # get_best_teams(team_all)
    # build_teams_history()
    # team_history = get_team_history("CHI")
    # print(team_history)

    # players = pd.read_csv("./data/new_data/player_season.csv")
    #
    # result = get_season_best_players("16-17")
    # print(result)
    # print(get_season_best_players("16-17"))
    # # get_player_season_data('James Harden', '16-17')
    #
    # init_mysql()
    # # write_players_to_mysql(players.tolist())
    # write_seasons_to_mysql(players["赛季"].unique().tolist())
    # deinit_mysql()

    # write_to_mysql()

    # get_best_teams()
    # player_history_data=get_player_history_data("Reggie Jordan")
    # print(player_history_data)
    # player_aggregation=get_player_aggregation()
    # print(player_aggregation)

    # build_mid_data()
    # print(get_season_team_rank("16-17"))
    # get_teams_history_data()
    # print(get_season_best_players("16-17"))
    # print(get_season_best_players_rank("16-17"))
    # print(get_season_playoff_team_top("16-17"))
    # print(get_best_teams())
    # print(get_best_players())
    # print(get_best_teams())
    # get_teams_data()
    # print(get_team_history("LAL"))
    print(get_team_player_salary("LAL"))
    # print(get_player_list())
    # print(get_player_history_agg("Kobe Bryant"))
    # print(get_player_season_data("Kobe Bryant", "15-16"))
